﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using GMAC.ReportingSystem.Business.Entities;
using FrameworkManapro.BusinessModel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using GMAC.ReportingSystem.Business.Entities.Configuration;

namespace GMAC.ReportingSystem.Business.Components
{
    public class PlantFileComponent : IBusinessComponent<StringPlantFile>
    {

        // Referencia la clase de datos
        private DataAccess.PlantFileDataAccess dac;

        // Constructor que instancia la capa de datos
        #region Constructor
            /// <summary>
            /// Constructor de la la clase de negocios que crea una instancia de la clase de datos
            /// </summary>
        public PlantFileComponent()
            {
                dac = new DataAccess.PlantFileDataAccess();
            } // PlantFileComponent
        #endregion

        // Metodos no Implementados pide que pide "IBusinessComponent"
        #region IBusinessComponent<StringPlantFile> Members

            /// <summary>
            /// Metodo no implementado
            /// </summary>
            /// <param name="entityToCreate"></param>
            /// <returns></returns>
            public int Create(StringPlantFile entityToCreate)
            {
                throw new NotImplementedException();
            } // Create

            /// <summary>
            /// Metodo no implementado
            /// </summary>
            /// <param name="entityToDelete"></param>
            /// <returns></returns>
            public int Delete(StringPlantFile entityToDelete)
            {
                throw new NotImplementedException();
            } // Delete

            /// <summary>
            /// Metodo no implementado
            /// </summary>
            /// <param name="entityToEdit"></param>
            /// <returns></returns>
            public int Edit(StringPlantFile entityToEdit)
            {
                throw new NotImplementedException();
            } // Edit

            /// <summary>
            /// Metodo no implementado
            /// </summary>
            /// <param name="entityId"></param>
            /// <returns></returns>
            public StringPlantFile GetDetails(object entityId)
            {
                throw new NotImplementedException();
            } // GetDetails

        #endregion


        #region "Metodos"

            /// <summary>
            /// Carga el archivo "Archivo de Planta" que contiene las ventas que se realizan a nivel nacional por concesionario
            /// </summary>
            /// <remarks>El archivo es de tipo Excel</remarks>
            /// <param name="processDate">Fecha en la que se esta procesando el archivo</param>
            /// <param name="fileName">Ruta y nombre del archivo a procesar</param>
            /// <param name="userId">Usuari oquer ejecuta la operacion</param>
            /// <param name="planInvalids">Lista de los registros invalidos que se encontraron en el archivo</param>
            /// <param name="planValids">Lista de los registros validos que se encontraron en el archivo</param>
            public void LoadFile(DateTime processDate, string fileName, int userId, ref StringPlantFileCollection planInvalids, ref StringPlantFileCollection planValids)
            {
                // Primera fila de los datos en el archivo
                int FIRST_ROW = 2;

                StringPlantFileCollection PlanRecords = new StringPlantFileCollection();

                //  asigna las columnas donde se encuentran los datos en el archivo excel
                string COL_1_PLANTFILERETAILINVOICEDATE			    = "E";
	            string COL_2_PLANTFILEDEALER						= "F";
	            string COL_3_PLANTFILEDEALERNUMBER					= "G";
	            string COL_4_PLANTFILEVEHICLEIDENTIFICATION		    = "H";
	            string COL_5_PLANTFILEMODELCODE					    = "I";
	            string COL_6_PLANTFILEMODELDESCRIPTION				= "J";
	            string COL_7_PLANTFILEWHOLESALEDISTRIBUTIONCHANNEL  = "K";
	            string COL_8_PLANTFILEESTATUS						= "L";
	            string COL_9_PLANTFILEPAYMENTFORM					= "M";
	            string COL_10_PLANTFILEPLAQUEPATENT					= "N";
	            string COL_11_PLANTFILECONTACTNAME					= "O";
	            string COL_12_PLANTFILECONTACTLASTNAME				= "P";
	            string COL_13_PLANTFILEYEAR							= "Q";
	            string COL_14_PLANTFILECONTACTADDRESS				= "R";
	            string COL_15_PLANTFILECONTACTMOBILEPHONE			= "S";
	            string COL_16_PLANTFILERESIDENCYTELEPHONECONTACT	= "T";
	            string COL_17_PLANTFILECONTACTCITY					= "U";
	            string COL_18_PLANTFILEDELIVERYDATE					= "V";
	            string COL_19_ZONEID								= "W";
	            string COL_20_PLANTFILESALESDETAIL					= "X";
	            string COL_21_PLANTFILESECTORSALES					= "Y";
                string COL_22_PLANTFILETYPE                         = "Z";


                var oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                var app = new Excel.Application();
                Excel.Workbook wb = null;
                var missing = Type.Missing;
                StringPlantFile PlanRecord;

                try
                {
                    string strCulture = System.Configuration.ConfigurationManager.AppSettings["Culture"];
                    System.Globalization.CultureInfo infCulture = new System.Globalization.CultureInfo(strCulture);

                    wb = app.Workbooks.Open(fileName, missing, true, missing, missing,
                        missing, true, missing, missing, missing, false,
                        missing, false, true, false);
                    var sheet = wb.Sheets[17] as Excel.Worksheet;
                    if (sheet != null)
                    {
                        int i = FIRST_ROW;

                        // Formatea las letras de las columnas en el excel de las que proviene los valores
                        var cell1_PLANTFILERETAILINVOICEDATE            = string.Format("{0}{1}", COL_1_PLANTFILERETAILINVOICEDATE, i);
                        var cell2_PLANTFILEDEALER                       = string.Format("{0}{1}", COL_2_PLANTFILEDEALER, i);
                        var cell3_PLANTFILEDEALERNUMBER                 = string.Format("{0}{1}", COL_3_PLANTFILEDEALERNUMBER, i);
                        var cell4_PLANTFILEVEHICLEIDENTIFICATION        = string.Format("{0}{1}", COL_4_PLANTFILEVEHICLEIDENTIFICATION, i);
                        var cell5_PLANTFILEMODELCODE                    = string.Format("{0}{1}", COL_5_PLANTFILEMODELCODE, i);
                        var cell6_PLANTFILEMODELDESCRIPTION             = string.Format("{0}{1}", COL_6_PLANTFILEMODELDESCRIPTION, i);
                        var cell7_PLANTFILEWHOLESALEDISTRIBUTIONCHANNEL = string.Format("{0}{1}", COL_7_PLANTFILEWHOLESALEDISTRIBUTIONCHANNEL, i);
                        var cell8_PLANTFILEESTATUS                      = string.Format("{0}{1}", COL_8_PLANTFILEESTATUS, i);
                        var cell9_PLANTFILEPAYMENTFORM                  = string.Format("{0}{1}", COL_9_PLANTFILEPAYMENTFORM, i);
                        var cell10_PLANTFILEPLAQUEPATENT                = string.Format("{0}{1}", COL_10_PLANTFILEPLAQUEPATENT, i);
                        var cell11_PLANTFILECONTACTNAME                 = string.Format("{0}{1}", COL_11_PLANTFILECONTACTNAME, i);
                        var cell12_PLANTFILECONTACTLASTNAME             = string.Format("{0}{1}", COL_12_PLANTFILECONTACTLASTNAME, i);
                        var cell13_PLANTFILEYEAR                        = string.Format("{0}{1}", COL_13_PLANTFILEYEAR, i);
                        var cell14_PLANTFILECONTACTADDRESS              = string.Format("{0}{1}", COL_14_PLANTFILECONTACTADDRESS, i);
                        var cell15_PLANTFILECONTACTMOBILEPHONE          = string.Format("{0}{1}", COL_15_PLANTFILECONTACTMOBILEPHONE, i);
                        var cell16_PLANTFILERESIDENCYTELEPHONECONTACT   = string.Format("{0}{1}", COL_16_PLANTFILERESIDENCYTELEPHONECONTACT, i);
                        var cell17_PLANTFILECONTACTCITY                 = string.Format("{0}{1}", COL_17_PLANTFILECONTACTCITY, i);
                        var cell18_PLANTFILEDELIVERYDATE                = string.Format("{0}{1}", COL_18_PLANTFILEDELIVERYDATE, i);
                        var cell19_ZONEID                               = string.Format("{0}{1}", COL_19_ZONEID, i);
                        var cell20_PLANTFILESALESDETAIL                 = string.Format("{0}{1}", COL_20_PLANTFILESALESDETAIL, i);
                        var cell21_PLANTFILESECTORSALES                 = string.Format("{0}{1}", COL_21_PLANTFILESECTORSALES, i);
                        var cell22_PLANTFILETYPE                        = string.Format("{0}{1}", COL_22_PLANTFILETYPE, i);

                        // declare las variables donde se capturaran los valores de cada filas del excel
                        string str_cell1_PLANTFILERETAILINVOICEDATE             = sheet.get_Range(cell1_PLANTFILERETAILINVOICEDATE              , cell1_PLANTFILERETAILINVOICEDATE).Text.ToString().Trim();
                        string str_cell2_PLANTFILEDEALER                        = sheet.get_Range(cell2_PLANTFILEDEALER                         , cell2_PLANTFILEDEALER).Text.ToString().Trim();
                        string str_cell3_PLANTFILEDEALERNUMBER                  = sheet.get_Range(cell3_PLANTFILEDEALERNUMBER                   , cell3_PLANTFILEDEALERNUMBER).Text.ToString().Trim();
                        string str_cell4_PLANTFILEVEHICLEIDENTIFICATION         = sheet.get_Range(cell4_PLANTFILEVEHICLEIDENTIFICATION          , cell4_PLANTFILEVEHICLEIDENTIFICATION).Text.ToString().Trim();
                        string str_cell5_PLANTFILEMODELCODE                     = sheet.get_Range(cell5_PLANTFILEMODELCODE                      , cell5_PLANTFILEMODELCODE).Text.ToString().Trim();
                        string str_cell6_PLANTFILEMODELDESCRIPTION              = sheet.get_Range(cell6_PLANTFILEMODELDESCRIPTION               , cell6_PLANTFILEMODELDESCRIPTION).Text.ToString().Trim();
                        string str_cell7_PLANTFILEWHOLESALEDISTRIBUTIONCHANNEL  = sheet.get_Range(cell7_PLANTFILEWHOLESALEDISTRIBUTIONCHANNEL   , cell7_PLANTFILEWHOLESALEDISTRIBUTIONCHANNEL).Text.ToString().Trim();
                        string str_cell8_PLANTFILEESTATUS                       = sheet.get_Range(cell8_PLANTFILEESTATUS                        , cell8_PLANTFILEESTATUS).Text.ToString().Trim();
                        string str_cell9_PLANTFILEPAYMENTFORM                   = sheet.get_Range(cell9_PLANTFILEPAYMENTFORM                    , cell9_PLANTFILEPAYMENTFORM).Text.ToString().Trim();
                        string str_cell10_PLANTFILEPLAQUEPATENT                 = sheet.get_Range(cell10_PLANTFILEPLAQUEPATENT                  , cell10_PLANTFILEPLAQUEPATENT).Text.ToString().Trim();
                        string str_cell11_PLANTFILECONTACTNAME                  = sheet.get_Range(cell11_PLANTFILECONTACTNAME                   , cell11_PLANTFILECONTACTNAME).Text.ToString().Trim();
                        string str_cell12_PLANTFILECONTACTLASTNAME              = sheet.get_Range(cell12_PLANTFILECONTACTLASTNAME               , cell12_PLANTFILECONTACTLASTNAME).Text.ToString().Trim();
                        string str_cell13_PLANTFILEYEAR                         = sheet.get_Range(cell13_PLANTFILEYEAR                          , cell13_PLANTFILEYEAR).Text.ToString().Trim();
                        string str_cell14_PLANTFILECONTACTADDRESS               = sheet.get_Range(cell14_PLANTFILECONTACTADDRESS                , cell14_PLANTFILECONTACTADDRESS).Text.ToString().Trim();
                        string str_cell15_PLANTFILECONTACTMOBILEPHONE           = sheet.get_Range(cell15_PLANTFILECONTACTMOBILEPHONE            , cell15_PLANTFILECONTACTMOBILEPHONE).Text.ToString().Trim();
                        string str_cell16_PLANTFILERESIDENCYTELEPHONECONTACT    = sheet.get_Range(cell16_PLANTFILERESIDENCYTELEPHONECONTACT     , cell16_PLANTFILERESIDENCYTELEPHONECONTACT).Text.ToString().Trim();
                        string str_cell17_PLANTFILECONTACTCITY                  = sheet.get_Range(cell17_PLANTFILECONTACTCITY                   , cell17_PLANTFILECONTACTCITY).Text.ToString().Trim();
                        string str_cell18_PLANTFILEDELIVERYDATE                 = sheet.get_Range(cell18_PLANTFILEDELIVERYDATE                  , cell18_PLANTFILEDELIVERYDATE).Text.ToString().Trim();
                        string str_cell19_ZONEID                                = sheet.get_Range(cell19_ZONEID                                 , cell19_ZONEID).Text.ToString().Trim();
                        string str_cell20_PLANTFILESALESDETAIL                  = sheet.get_Range(cell20_PLANTFILESALESDETAIL                   , cell20_PLANTFILESALESDETAIL).Text.ToString().Trim();
                        string str_cell21_PLANTFILESECTORSALES                  = sheet.get_Range(cell21_PLANTFILESECTORSALES                   , cell21_PLANTFILESECTORSALES).Text.ToString().Trim();
                        string str_cell22_PLANTFILETYPE                         = sheet.get_Range(cell22_PLANTFILETYPE                          , cell22_PLANTFILETYPE).Text.ToString().Trim();

                        // Se hara el while mientras exista por lo menos un campo con longitudes > 0
                        while (   str_cell1_PLANTFILERETAILINVOICEDATE.Length               > 0
                               || str_cell2_PLANTFILEDEALER.Length                          > 0
                               || str_cell3_PLANTFILEDEALERNUMBER.Length                    > 0
                               || str_cell4_PLANTFILEVEHICLEIDENTIFICATION.Length           > 0
                               || str_cell5_PLANTFILEMODELCODE.Length                       > 0
                               || str_cell6_PLANTFILEMODELDESCRIPTION.Length                > 0
                               || str_cell7_PLANTFILEWHOLESALEDISTRIBUTIONCHANNEL.Length    > 0
                               || str_cell8_PLANTFILEESTATUS.Length                         > 0
                               || str_cell9_PLANTFILEPAYMENTFORM.Length                     > 0
                               || str_cell10_PLANTFILEPLAQUEPATENT.Length                   > 0
                               || str_cell11_PLANTFILECONTACTNAME.Length                    > 0
                               || str_cell12_PLANTFILECONTACTLASTNAME.Length                > 0
                               || str_cell13_PLANTFILEYEAR.Length                           > 0
                               || str_cell14_PLANTFILECONTACTADDRESS.Length                 > 0
                               || str_cell15_PLANTFILECONTACTMOBILEPHONE.Length             > 0
                               || str_cell16_PLANTFILERESIDENCYTELEPHONECONTACT.Length      > 0
                               || str_cell17_PLANTFILECONTACTCITY.Length                    > 0
                               || str_cell18_PLANTFILEDELIVERYDATE.Length                   > 0
                               || str_cell19_ZONEID.Length                                  > 0
                               || str_cell20_PLANTFILESALESDETAIL.Length                    > 0
                               || str_cell21_PLANTFILESECTORSALES.Length                    > 0
                               || str_cell22_PLANTFILETYPE.Length                           > 0
                              ) // while
                        {
                            // Inicializa la cadena que captura las razones de rechazo
                            //strRejectionReason = "";

                            // Instancia los objetos para capturar una fila de el archivo e insertarlos en la coleccion de los validos o los invalidos
                            PlanRecord = new StringPlantFile();

                            PlanRecord.LoadDate = processDate;
                            PlanRecord.RetailInvoiceDate            = str_cell1_PLANTFILERETAILINVOICEDATE;
                            PlanRecord.Dealer                       = str_cell2_PLANTFILEDEALER;
                            PlanRecord.DealerNumber                 = str_cell3_PLANTFILEDEALERNUMBER;
                            PlanRecord.VehicleIdentification        = str_cell4_PLANTFILEVEHICLEIDENTIFICATION;
                            PlanRecord.ModelCode                    = str_cell5_PLANTFILEMODELCODE;
                            PlanRecord.ModelDescription             = str_cell6_PLANTFILEMODELDESCRIPTION;
                            PlanRecord.WholesaleDistributionChannel = str_cell7_PLANTFILEWHOLESALEDISTRIBUTIONCHANNEL;
                            PlanRecord.Estatus                      = str_cell8_PLANTFILEESTATUS;
                            PlanRecord.PaymentForm                  = str_cell9_PLANTFILEPAYMENTFORM;
                            PlanRecord.PlaquePatent                 = str_cell10_PLANTFILEPLAQUEPATENT;
                            PlanRecord.ContactName                  = str_cell11_PLANTFILECONTACTNAME;
                            PlanRecord.ContactLastName              = str_cell12_PLANTFILECONTACTLASTNAME;
                            PlanRecord.Year                         = str_cell13_PLANTFILEYEAR;
                            PlanRecord.ContactAddress               = str_cell14_PLANTFILECONTACTADDRESS;
                            PlanRecord.ContactMobilePhone           = str_cell15_PLANTFILECONTACTMOBILEPHONE;
                            PlanRecord.residencyTelephoneContact    = str_cell16_PLANTFILERESIDENCYTELEPHONECONTACT;
                            PlanRecord.ContactCity                  = str_cell17_PLANTFILECONTACTCITY;
                            PlanRecord.DeliveryDate                 = str_cell18_PLANTFILEDELIVERYDATE;
                            PlanRecord.ZoneID                       = str_cell19_ZONEID;
                            PlanRecord.SalesDetail                  = str_cell20_PLANTFILESALESDETAIL;
                            PlanRecord.SectorSales                  = str_cell21_PLANTFILESECTORSALES;
                            PlanRecord.PlanFileType                 = str_cell22_PLANTFILETYPE;

                            // Agrega el Item a la coleccion
                            PlanRecords.Add(PlanRecord);

                            // Se mueve al siguiente registro del while
                            i++;

                            // Formatea las letras de las columnas en el excel de las que proviene los valores
                            cell1_PLANTFILERETAILINVOICEDATE            = string.Format("{0}{1}", COL_1_PLANTFILERETAILINVOICEDATE, i);
                            cell2_PLANTFILEDEALER                       = string.Format("{0}{1}", COL_2_PLANTFILEDEALER, i);
                            cell3_PLANTFILEDEALERNUMBER                 = string.Format("{0}{1}", COL_3_PLANTFILEDEALERNUMBER, i);
                            cell4_PLANTFILEVEHICLEIDENTIFICATION        = string.Format("{0}{1}", COL_4_PLANTFILEVEHICLEIDENTIFICATION, i);
                            cell5_PLANTFILEMODELCODE                    = string.Format("{0}{1}", COL_5_PLANTFILEMODELCODE, i);
                            cell6_PLANTFILEMODELDESCRIPTION             = string.Format("{0}{1}", COL_6_PLANTFILEMODELDESCRIPTION, i);
                            cell7_PLANTFILEWHOLESALEDISTRIBUTIONCHANNEL = string.Format("{0}{1}", COL_7_PLANTFILEWHOLESALEDISTRIBUTIONCHANNEL, i);
                            cell8_PLANTFILEESTATUS                      = string.Format("{0}{1}", COL_8_PLANTFILEESTATUS, i);
                            cell9_PLANTFILEPAYMENTFORM                  = string.Format("{0}{1}", COL_9_PLANTFILEPAYMENTFORM, i);
                            cell10_PLANTFILEPLAQUEPATENT                = string.Format("{0}{1}", COL_10_PLANTFILEPLAQUEPATENT, i);
                            cell11_PLANTFILECONTACTNAME                 = string.Format("{0}{1}", COL_11_PLANTFILECONTACTNAME, i);
                            cell12_PLANTFILECONTACTLASTNAME             = string.Format("{0}{1}", COL_12_PLANTFILECONTACTLASTNAME, i);
                            cell13_PLANTFILEYEAR                        = string.Format("{0}{1}", COL_13_PLANTFILEYEAR, i);
                            cell14_PLANTFILECONTACTADDRESS              = string.Format("{0}{1}", COL_14_PLANTFILECONTACTADDRESS, i);
                            cell15_PLANTFILECONTACTMOBILEPHONE          = string.Format("{0}{1}", COL_15_PLANTFILECONTACTMOBILEPHONE, i);
                            cell16_PLANTFILERESIDENCYTELEPHONECONTACT   = string.Format("{0}{1}", COL_16_PLANTFILERESIDENCYTELEPHONECONTACT, i);
                            cell17_PLANTFILECONTACTCITY                 = string.Format("{0}{1}", COL_17_PLANTFILECONTACTCITY, i);
                            cell18_PLANTFILEDELIVERYDATE                = string.Format("{0}{1}", COL_18_PLANTFILEDELIVERYDATE, i);
                            cell19_ZONEID                               = string.Format("{0}{1}", COL_19_ZONEID, i);
                            cell20_PLANTFILESALESDETAIL                 = string.Format("{0}{1}", COL_20_PLANTFILESALESDETAIL, i);
                            cell21_PLANTFILESECTORSALES                 = string.Format("{0}{1}", COL_21_PLANTFILESECTORSALES, i);
                            cell22_PLANTFILETYPE                        = string.Format("{0}{1}", COL_22_PLANTFILETYPE, i);

                            // vuelve a capturar los valores de la siguiente fila del excel para el while
                            str_cell1_PLANTFILERETAILINVOICEDATE            = sheet.get_Range(cell1_PLANTFILERETAILINVOICEDATE              , cell1_PLANTFILERETAILINVOICEDATE).Text.ToString().Trim();
                            str_cell2_PLANTFILEDEALER                       = sheet.get_Range(cell2_PLANTFILEDEALER                         , cell2_PLANTFILEDEALER).Text.ToString().Trim();
                            str_cell3_PLANTFILEDEALERNUMBER                 = sheet.get_Range(cell3_PLANTFILEDEALERNUMBER                   , cell3_PLANTFILEDEALERNUMBER).Text.ToString().Trim();
                            str_cell4_PLANTFILEVEHICLEIDENTIFICATION        = sheet.get_Range(cell4_PLANTFILEVEHICLEIDENTIFICATION          , cell4_PLANTFILEVEHICLEIDENTIFICATION).Text.ToString().Trim();
                            str_cell5_PLANTFILEMODELCODE                    = sheet.get_Range(cell5_PLANTFILEMODELCODE                      , cell5_PLANTFILEMODELCODE).Text.ToString().Trim();
                            str_cell6_PLANTFILEMODELDESCRIPTION             = sheet.get_Range(cell6_PLANTFILEMODELDESCRIPTION               , cell6_PLANTFILEMODELDESCRIPTION).Text.ToString().Trim();
                            str_cell7_PLANTFILEWHOLESALEDISTRIBUTIONCHANNEL = sheet.get_Range(cell7_PLANTFILEWHOLESALEDISTRIBUTIONCHANNEL   , cell7_PLANTFILEWHOLESALEDISTRIBUTIONCHANNEL).Text.ToString().Trim();
                            str_cell8_PLANTFILEESTATUS                      = sheet.get_Range(cell8_PLANTFILEESTATUS                        , cell8_PLANTFILEESTATUS).Text.ToString().Trim();
                            str_cell9_PLANTFILEPAYMENTFORM                  = sheet.get_Range(cell9_PLANTFILEPAYMENTFORM                    , cell9_PLANTFILEPAYMENTFORM).Text.ToString().Trim();
                            str_cell10_PLANTFILEPLAQUEPATENT                = sheet.get_Range(cell10_PLANTFILEPLAQUEPATENT                  , cell10_PLANTFILEPLAQUEPATENT).Text.ToString().Trim();
                            str_cell11_PLANTFILECONTACTNAME                 = sheet.get_Range(cell11_PLANTFILECONTACTNAME                   , cell11_PLANTFILECONTACTNAME).Text.ToString().Trim();
                            str_cell12_PLANTFILECONTACTLASTNAME             = sheet.get_Range(cell12_PLANTFILECONTACTLASTNAME               , cell12_PLANTFILECONTACTLASTNAME).Text.ToString().Trim();
                            str_cell13_PLANTFILEYEAR                        = sheet.get_Range(cell13_PLANTFILEYEAR                          , cell13_PLANTFILEYEAR).Text.ToString().Trim();
                            str_cell14_PLANTFILECONTACTADDRESS              = sheet.get_Range(cell14_PLANTFILECONTACTADDRESS                , cell14_PLANTFILECONTACTADDRESS).Text.ToString().Trim();
                            str_cell15_PLANTFILECONTACTMOBILEPHONE          = sheet.get_Range(cell15_PLANTFILECONTACTMOBILEPHONE            , cell15_PLANTFILECONTACTMOBILEPHONE).Text.ToString().Trim();
                            str_cell16_PLANTFILERESIDENCYTELEPHONECONTACT   = sheet.get_Range(cell16_PLANTFILERESIDENCYTELEPHONECONTACT     , cell16_PLANTFILERESIDENCYTELEPHONECONTACT).Text.ToString().Trim();
                            str_cell17_PLANTFILECONTACTCITY                 = sheet.get_Range(cell17_PLANTFILECONTACTCITY                   , cell17_PLANTFILECONTACTCITY).Text.ToString().Trim();
                            str_cell18_PLANTFILEDELIVERYDATE                = sheet.get_Range(cell18_PLANTFILEDELIVERYDATE                  , cell18_PLANTFILEDELIVERYDATE).Text.ToString().Trim();
                            str_cell19_ZONEID                               = sheet.get_Range(cell19_ZONEID                                 , cell19_ZONEID).Text.ToString().Trim();
                            str_cell20_PLANTFILESALESDETAIL                 = sheet.get_Range(cell20_PLANTFILESALESDETAIL                   , cell20_PLANTFILESALESDETAIL).Text.ToString().Trim();
                            str_cell21_PLANTFILESECTORSALES                 = sheet.get_Range(cell21_PLANTFILESECTORSALES                   , cell21_PLANTFILESECTORSALES).Text.ToString().Trim();
                            str_cell22_PLANTFILETYPE                        = sheet.get_Range(cell22_PLANTFILETYPE                          , cell22_PLANTFILETYPE).Text.ToString().Trim();

                        } // while

                        dac.UploadPlanFile(processDate, userId, PlanRecords, ref planInvalids, ref planValids);
                    } // if (sheet != null)
                    else
                    {
                        throw new ApplicationException("File invalid.");
                    }
                } // try
                catch
                {
                    throw;
                } // catch
                finally
                {
                    if (wb != null)
                    {
                        wb.Close(false, missing, missing);
                        Marshal.FinalReleaseComObject(wb);
                    } // if
                    if (true)
                    {
                        app.Quit();
                        Marshal.FinalReleaseComObject(app);
                    } // if
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                    System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;

                } // finally
            } // LoadFile

            /// <summary>
            /// Metodo que inserta los registros valios del archivo excel
            /// </summary>
            /// <param name="typeFile">Tipo de archivo</param>
            /// <param name="UserId">Usuario que procesa el archivo</param>
            public void GenerateUploadFile(int UserId)
            {
                dac.GenerateUploadFile(UserId);
            } // GenerateUploadFile

            /// <summary>
            /// Recibe una coleccion y la retorna ordenada segun indiquen los parametros
            /// </summary>
            /// <param name="records">Coleccion de valores a ordenar</param>
            /// <param name="column">Campo por el que va a ordenar</param>
            /// <param name="order">"ASC" o "DESC"</param>
            /// <returns>Lista ordenada</returns>
            public StringPlantFileCollection sord(StringPlantFileCollection records, string column, string order)
            {
                return dac.sord(records, column, order);
            } // sord

            /// <summary>
            /// Valida si los datos son numericos
            /// </summary>
            /// <param name="value">Valor a validar</param>
            /// <returns>
            /// true: Es Valido
            /// false: No es Valido
            /// </returns>
            public bool IsNumeric(string value)
            {
                try
                {
                    long.Parse(value);
                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
            } // IsNumeric

            /// <summary>
            /// Valida si los datos son Decimales
            /// </summary>
            /// <param name="value">Valor a validar</param>
            /// <returns>
            /// true: Es Valido
            /// false: No es Valido
            /// </returns>
            public bool IsDecimal(string value)
            {
                try
                {
                    decimal.Parse(value);
                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
            } // IsDecimal

            /// <summary>
            /// Valida si los datos son Date
            /// </summary>
            /// <param name="value">Valor a validar</param>
            /// <returns>
            /// true: Es Valido
            /// false: No es Valido
            /// </returns>
            public bool IsDate(string value)
            {
                try
                {
                    string strCulture = System.Configuration.ConfigurationManager.AppSettings["Culture"];

                    DateTime.Parse(value, new System.Globalization.CultureInfo(strCulture));

                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
            } // IsDate
        #endregion

    } // class
} // namespace
